using System;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Reflection;
using GMID = System.Int64;

namespace Cobalt75.GreyMatter
{
   
    public class GMCommunicator : IDisposable
    {
        


        private Dictionary<Type, Dictionary<GMID, GMTable>> pIDCache;
        private Dictionary<Type, Dictionary<string, GMTableNamed>> pNameCache;
        private Dictionary<Type, Dictionary<GMID, Int64>> pIDCacheTicks;
        private Dictionary<Type, Dictionary<string, Int64>> pNameCacheTicks;
        private Type[] pStandardConstructorTypes = new Type[] { typeof(GMCommunicator) };
        
        string pDatabase;
        IDbConnection pConnection;
        public IDbConnection Connection
        {
            get
            {
                return pConnection;
            }
            set
            {
                pConnection = value;
            }
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                CloseAndDispose();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(true);
        }

        public void CloseAndDispose()
        {
            pConnection.Close();
            pConnection.Dispose();
        }
        Dictionary<Type, GMTableAttribute> pReflectionCache;
        protected Dictionary<Type, GMTableAttribute> ReflectionCache
        {
            get
            {
                return (pReflectionCache);
            }
        }
        public string TableNameFor(Type type)
        {
            return pReflectionCache[type].TableName;
        }

        Collection<GMLinkTable> pLinkTables;

        protected Collection<GMLinkTable> LinkTables
        {
            get
            {
                return (pLinkTables);
            }
        }
        public string Database
        {
            get {
                return pDatabase;
            }
        }
        public GMCommunicator(string database)
        {
            pDatabase = database;
        }
        public void CreateDBSchemaAndReport()
        {
            Console.WriteLine("GM Com - GenerateReflectionCache");
            GenerateReflectionCache();
            Console.WriteLine("GM Com - CreateDBSchema");
            CreateDBSchema();
            Console.WriteLine("GM Com - CreateStaticValues");
            CreateStaticValues();
        }

        #region High-Level Lookup Database Methods
        public virtual T SelectFromId<T>(GMID id) where T : GMTable
        {
            return (SelectFromId<T>(id, false));
        }
        public virtual T SelectFromId<T>(GMID id,Type t) where T : GMTable
        {
            return (SelectFromId<T>(id, false));
        }
        public virtual T SelectFromId<T>(GMID id, bool noCache) where T : GMTable
        {
            Type ttype = typeof(T);
            if (!noCache && pIDCache[ttype].ContainsKey(id))
            {
                Int64 OldTicks = pIDCacheTicks[ttype][id];
                Int64 NewTicks = GetTableSync(ttype);
                if (NewTicks < OldTicks)
                {
                    pIDCache[ttype].Remove(id);
                }
            }
            if (!pIDCache[ttype].ContainsKey(id) || noCache)
            {


                string tableName = pReflectionCache[ttype].TableName;
                GMQuery gmq = new GMQuery(GMQueryCommand.Select);
                gmq.pTables.Add(tableName);
                gmq.AddReturnColumns(GMQueryClauseItemReference.All(tableName));
                
                GMQueryClause clause=  new GMQueryClause();
                clause.ClauseOperator = GMQueryClauseOperator.Equals;
                //clause.ClauseType = GMQClauseType.ColumnParameter;
                clause.ItemA = GMQueryClauseItemReference.Column(tableName,"id");
                clause.ItemB = GMQueryClauseItemReference.Parameter("id", id);
                gmq.AddWhereClauses(clause);
          
                T t = Select<T>(gmq)[0];
                pIDCache[ttype][id] = t;
                pIDCacheTicks[ttype][id] = GetTableSync(ttype);
                return t;
            }
            return ((T)pIDCache[ttype][id]);
        }

        public virtual T SelectFromName<T>(string name) where T : GMTableNamed
        {
            return (SelectFromName<T>(name, false));
        }
        public virtual T SelectFromName<T>(string name, bool noCache) 
            where T : GMTableNamed
        {
            Type ttype = typeof(T);
            if (!noCache && pNameCache[ttype].ContainsKey(name))
            {
                Int64 OldTicks = pNameCacheTicks[ttype][name];
                Int64 NewTicks = GetTableSync(ttype);
                if (NewTicks < OldTicks)
                {
                    pNameCache[ttype].Remove(name);
                }
            }
            if (!pNameCache[ttype].ContainsKey(name) || noCache)
            {

                string tableName = pReflectionCache[typeof(T)].TableName;
                GMQuery gmq = new GMQuery(GMQueryCommand.Select);
                gmq.pTables.Add(tableName);
                gmq.AddReturnColumns(GMQueryClauseItemReference.All(tableName));
                GMQueryClause clause = new GMQueryClause();
                clause.ClauseOperator = GMQueryClauseOperator.Equals;
                //clause.ClauseType = GMQClauseType.ColumnParameter;
                clause.ItemA = GMQueryClauseItemReference.Column(tableName, "name");
                clause.ItemB = GMQueryClauseItemReference.Parameter("name", name);
                gmq.AddWhereClauses(clause);


                T t = Select<T>(gmq)[0];
                pNameCache[ttype][name] = t;
                pNameCacheTicks[ttype][name] = GetTableSync(ttype);
                return t;
            }
            return ((T)pNameCache[ttype][name]);
        }
        public virtual string TempIdTableCreationSql()
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        public virtual T Select<T>(params object[] queryParameters) where T : GMTable
        {
            GMQuery gmq = new GMQuery(GMQueryCommand.Select);
            gmq.AddTable(TableNameFor(typeof(T)));
            gmq.AddReturnColumns(GMQueryClauseItemReference.All(gmq.Tables()[0]));
            GMQueryClause clause = new GMQueryClause();
            clause.ClauseOperator = GMQueryClauseOperator.Equals;
            
            for (int i = 0; i < queryParameters.Length; i++)
            {
                clause.ItemA = GMQueryClauseItemReference.Column(gmq.Tables()[0], queryParameters[i].ToString());
                clause.ItemB = GMQueryClauseItemReference.Parameter(queryParameters[i].ToString(), queryParameters[i+1]);
                i++;
            }
            gmq.AddWhereClauses(clause);
            T[] objs = Select<T>(gmq);
            if (objs.Length == 0)
            {
                return null;
            }
            if (objs.Length > 1)
            {
                throw (new GMAmbigiousException());
            }
            return objs[0];
        }
        public virtual T[] Select<T>(GMID[] ids) where T : GMTable
        {

            T[] tObjs;
       
            string tableName = pReflectionCache[typeof(T)].TableName;
            GMQuery gmq = new GMQuery(GMQueryCommand.Select);
            gmq.pTables.Add(tableName);
            gmq.AddReturnColumns(GMQueryClauseItemReference.All(tableName));
            GMQueryClause c = new GMQueryClause();
     

            c.ClauseOperator = GMQueryClauseOperator.Like;
            //w.ClauseType = GMQClauseType.ParameterFunction;
            c.ItemA = GMQueryClauseItemReference.Parameter("ids", "," + String.Join(",",Array.ConvertAll<GMID, string>(ids, new Converter<GMID, string>(Convert.ToString))) + ",");

            c.ItemB = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
            c.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal("%,"));
            c.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Column(tableName, "id"));
            c.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(",%"));

            gmq.AddWhereClauses(c);
            tObjs = Select<T>(gmq);
     
            return (tObjs);
        }
        public virtual T[] Select<T>(GMQuery query) where T : GMTable
        {

            IDbCommand Command = CommandFromQuery(query);
            Command.Connection = pConnection;
            //Command.Transaction = pConnection.BeginTransaction();
            T[] tObjs = Select<T>(Command);
            //Command.Transaction.Dispose();
            Command.Dispose();

            return (tObjs);
        }
        public virtual GMTable[] Select(GMQuery query,Type type)
        {

            IDbCommand Command = CommandFromQuery(query);
            Command.Connection = pConnection;
            //Command.Transaction = pConnection.BeginTransaction();
            GMTable[] tObjs = Select(Command, type);
            //Command.Transaction.Dispose();
            Command.Dispose();

            return (tObjs);
        }
        public virtual GMID[] SelectIDs(GMQuery query)
        {
            IDbCommand Command = CommandFromQuery(query);
            Command.Connection = pConnection;
            //Command.Transaction = pConnection.BeginTransaction();
            DataTable dT = GetTypedDataTable(Command);
            GMID[] tObjs = new GMID[dT.Rows.Count];
            for (int i = 0; i < tObjs.Length; i++)
            {
                tObjs[i] = (GMID)DBToClr(dT.Rows[i]["id"], null);
            }
            //Command.Transaction.Dispose();
            Command.Dispose();
            return (tObjs);
        }
        public virtual T[] SelectParameters<T>(GMQuery query)
        {
            IDbCommand Command = CommandFromQuery(query);
            Command.Connection = pConnection;
            //Command.Transaction = pConnection.BeginTransaction();
            DataTable dT = GetTypedDataTable(Command);
            T[] tObjs = new T[dT.Rows.Count];
            for (int i = 0; i < tObjs.Length; i++)
            {
                tObjs[i] = (T)DBToClr(dT.Rows[i]["id"], null);
            }
            //Command.Transaction.Dispose();
            Command.Dispose();
            return (tObjs);
        }
        
        #endregion

        #region Methods that MUST be overloaded
        protected virtual object ClrToDB(object obj, GMColumnBaseAttribute attr)
        {

            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        protected virtual object DBToClr(object obj, GMColumnBaseAttribute attr)
        {

            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        public virtual IDataParameter CreateParameter(string paraName, object paraValue)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));

        }
        public virtual void CreateDBSchema()
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));

        }
        public virtual string CreateTableSql(GMTableAttribute tableAttr)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));

        }
        public virtual string CreateTableSql(GMLinkTable linkTableAttr)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));

        }
        protected virtual void SetDatabaseSchemaField(Type type, ref GMColumnBaseAttribute attribute)
        {

            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        public virtual IDbCommand CommandFromQuery(GMQuery query)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));

        }
        protected virtual string ToString(GMQueryClause clause)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        #endregion
        #region Methods used in DDL (Can be overloaded)

        public virtual char QuoteChar
        {
            get
            {
                return '`';
            }
        }
        public virtual char ParameterChar
        {
            get
            {
                return '?';
            }
        }
        public virtual string Quote(string tableName)
        {
            if (tableName == "*")
                return "*";
            return (QuoteChar.ToString() + tableName + QuoteChar.ToString());
        }
        public virtual string Quote(string[] items)
        {

            return (String.Join(",", Array.ConvertAll<string, string>(items, new Converter<string, string>(Quote))));
        }
        public virtual string Quote(string tableName,string alias)
        {
            return (QuoteChar.ToString() + tableName + QuoteChar.ToString() + " " + alias);
        }

        protected virtual string ParameterNameFromId(string id)
        {
            if (id.StartsWith(ParameterChar.ToString()))
            {
                return id;
            } else {
                return (ParameterChar.ToString() + id);
            }
        }
        public virtual string GetFunctionFormatString(GMQuerySqlFunction function, params GMQueryClauseItemReference[] references)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        public virtual string GetFunctionFormatString(GMQuerySqlFunction function, Collection<GMQueryClauseItemReference> references)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        public virtual string ToString(GMQueryClauseItemReference itemReference)
        {
            switch (itemReference.ReferenceType)
            {
                case GMQueryClauseItemReferenceType.Column:
                    if (itemReference.ColumnAlias != null)
                        return (Quote(itemReference.TableName) + "." + Quote(itemReference.ColumnName,itemReference.ColumnAlias) );
                    return (Quote(itemReference.TableName) + "." + Quote(itemReference.ColumnName));
                case GMQueryClauseItemReferenceType.Function:
                    if (itemReference.FunctionFormatString == null)
                    {
                        return (GetFunctionFormatString(itemReference.SqlFunction, itemReference.FunctionParameters.ToArray()));
                    
                    }
                    else
                    {

                        return (String.Format(CultureInfo.InvariantCulture,itemReference.FunctionFormatString, ToString(itemReference.FunctionParameters.ToArray())));
                    }
                        case GMQueryClauseItemReferenceType.Parameter:
                    return (ParameterNameFromId(itemReference.ParameterName));
                case GMQueryClauseItemReferenceType.Literal:
                    return "'"+itemReference.LiteralString+"'";
                default:
                    return "??";
            }
        }
        public virtual string ToString(GMQueryClauseItemReference[] itemReferences)
        {
            return (String.Join(",", Array.ConvertAll<GMQueryClauseItemReference, string>(itemReferences, new Converter<GMQueryClauseItemReference, string>(ToString))));
        }

        public virtual string ToString(GMQueryClause[] clauses)
        {
            return (String.Join(Environment.NewLine + "AND" + Environment.NewLine, Array.ConvertAll<GMQueryClause, string>(clauses, new Converter<GMQueryClause, string>(ToString))));

        }

        public virtual void SetCommandFromQuery(GMQuery query, IDbCommand command)
        {
            StringBuilder sB = new StringBuilder();
            switch (query.pCommand)
            {
                case GMQueryCommand.Select:
                    sB.AppendLine("SELECT");
                    sB.AppendLine(ToString(query.ReturnColumns()));
                    sB.AppendLine("FROM");
                    sB.AppendLine(Quote(query.pTables.ToArray()));
                    GMQueryClause[] wclauses;
                    wclauses = query.WhereClauses();
                    if (wclauses.Length > 0)
                    {
                        sB.AppendLine("WHERE");
                        sB.AppendLine(ToString(wclauses));
                    }


                    GMQueryClauseItemReference[] gclauses;
                    gclauses = query.GroupClauses();
                    if (gclauses.Length > 0)
                    {
                        
                        sB.AppendLine("GROUP BY");
                        sB.AppendLine(ToString(gclauses));
                    }


                    //q.
                    //sB.AppendLine(q.);
                    break;
                case GMQueryCommand.Insert:

                    List<string> keys = new List<string>();
                    List<string> values = new List<string>();
                    foreach (GMQueryClause c in query.SetClauses())
                    {
                        keys.Add(c.ItemA.ColumnName);
                        values.Add(ToString(c.ItemB));
                    }
                    sB.AppendLine("INSERT INTO");
                    sB.AppendLine(Quote(query.pTables[0]));
                    sB.Append("(");
                    sB.Append(Quote(keys.ToArray()));
                    sB.AppendLine(")");
                    sB.AppendLine("VALUES");
                    sB.Append("(");
                    sB.Append(String.Join(",", values.ToArray()));
                    sB.AppendLine(")");
                    break;
                case GMQueryCommand.Update:
                    sB.AppendLine("UPDATE");
                    sB.AppendLine(Quote(query.pTables[0]));
                    sB.AppendLine("SET");
                    sB.AppendLine(String.Join(Environment.NewLine + "," + Environment.NewLine, Array.ConvertAll<GMQueryClause, string>(query.SetClauses(), new Converter<GMQueryClause, string>(ToString))));
                    sB.AppendLine("WHERE");
                    sB.AppendLine(ToString(query.WhereClauses()));
                    break;

                case GMQueryCommand.Delete:
                    sB.AppendLine("DELETE");
                    sB.AppendLine("FROM");
                    sB.AppendLine(Quote(query.pTables[0]));
                    sB.AppendLine("WHERE");
                    sB.AppendLine(ToString(query.WhereClauses()));
                    break;

                default:
                    break;

            }
            command.CommandText = sB.ToString();
            foreach (KeyValuePair<string, object> kp in query.Parameters())
            {
                command.Parameters.Add(CreateParameter(ParameterNameFromId(kp.Key), kp.Value));

            }
        }
#endregion
        #region High-Level Non-lookup Database Methods
        public void CreateDB(GMTable gmObject)
        {
            if (gmObject.Id != 0)
            {

                throw (new GMException("You cannot add this item - it's id is already set."));
            }
            Type objType = gmObject.GetType();
            GMTableAttribute attr = pReflectionCache[objType];


            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Insert);
            q.pTables.Add(attr.TableName);
            GMQueryClause clause;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in attr.Columns)
            {
                if (kp.Value.IsDatabaseColumn && !kp.Value.IsPrimary)
                {
                    clause = new GMQueryClause();
                    clause.ClauseOperator = GMQueryClauseOperator.Equals;
                    //clause.ClauseType = GMQClauseType.ColumnParameter;
                    clause.ItemA = GMQueryClauseItemReference.Column(attr.TableName, kp.Value.ColumnName);
                    clause.ItemB = GMQueryClauseItemReference.Parameter(kp.Value.ColumnName,ClrToDB(gmObject, kp.Value));
                    q.AddSetClauses(clause);
                }
            }
            IDbCommand Command = CommandFromQuery(q);

            //Command.Parameters[ParameterNameFromID("id")] = CreateParameter("id", null);

            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            gmObject.pId = GetLastInsertId(Command, attr.TableName);
            UpdateTableSync(attr.TableName, Command);
            trans.Commit();
            Command.Dispose();
            trans.Dispose();

        }

        public void CreateDB(GMTable[] gmObjects)
        {
            //Maybe need to perform check that all items are of same type?
            //Slow!
            //and maybe this check too
            /*
             
             */
            if (gmObjects.Length == 0)
                return;
            Type objType = gmObjects[0].GetType();
            foreach (GMTable obj in gmObjects)
            {
                if (obj.GetType() != objType)
                {
                    throw (new GMException("Bulk must be all same type"));
                }
                if (obj.Id != 0)
                {

                    throw (new GMException("You cannot add this item - it's id is already set."));
                }
            }
            GMTableAttribute attr = pReflectionCache[objType];
            IDbTransaction trans = pConnection.BeginTransaction();

            GMQuery q = new GMQuery(GMQueryCommand.Insert);
            q.pTables.Add(attr.TableName);
            GMQueryClause clause;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in attr.Columns)
            {
                if (kp.Value.IsDatabaseColumn && !kp.Value.IsPrimary)
                {
                    clause = new GMQueryClause();
                    clause.ClauseOperator = GMQueryClauseOperator.Equals;
                    //clause.ClauseType = GMQClauseType.ColumnParameter;
                    clause.ItemA = GMQueryClauseItemReference.Column(attr.TableName, kp.Value.ColumnName);
                    clause.ItemB = GMQueryClauseItemReference.Parameter(kp.Value.ColumnName, null);
                    q.AddSetClauses(clause);
                }
            }
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            foreach (GMTable obj in gmObjects)
            {
                foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in attr.Columns)
                {
                    if (kp.Value.IsDatabaseColumn && !kp.Value.IsPrimary)
                    {

                        Command.Parameters[ParameterNameFromId(kp.Value.ColumnName)] = CreateParameter(kp.Value.ColumnName,ClrToDB(obj, kp.Value));
                    }
                }
                //Command.Parameters[ParameterNameFromID("id")] = CreateParameter("id", null);

                Command.ExecuteNonQuery();
                obj.pId = GetLastInsertId(Command,attr.TableName);

                //q.InsertColumns.
                //ExecuteNonQuery(trans, q);
            }
            UpdateTableSync(attr.TableName, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();
        }
        public void UpdateDB(GMTable gmObject)
        {
            if (gmObject.Id == 0)
            {

                throw (new GMException("You cannot update this item - it's not on the database"));
            }
            Type objType = gmObject.GetType();
            //ReflectionCache[objType].
            GMTableAttribute attr = pReflectionCache[objType];


            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Update);
            q.pTables.Add(attr.TableName);
            //IDbCommand Command = pConnection.CreateCommand();
            //Command.Transaction = trans;
            GMQueryClause setC = new GMQueryClause();
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in attr.Columns)
            {
                if (kp.Value.IsDatabaseColumn)
                {
                    setC = new GMQueryClause();
                    setC.ClauseOperator = GMQueryClauseOperator.Equals;
                    //setC.ClauseType = GMQClauseType.ColumnParameter;
                    setC.ItemA = GMQueryClauseItemReference.Column(attr.TableName, kp.Value.ColumnName);
                    setC.ItemB = GMQueryClauseItemReference.Parameter(kp.Value.ColumnName, ClrToDB(gmObject, kp.Value));
                    q.AddSetClauses(setC);
                }
            }
            setC = new GMQueryClause();
            setC.ClauseOperator = GMQueryClauseOperator.Equals;
            //setC.ClauseType = GMQClauseType.ColumnParameter;
            setC.ItemA = GMQueryClauseItemReference.Column(attr.TableName, "id");
            setC.ItemB = GMQueryClauseItemReference.Parameter("p_id", gmObject.Id);
                    
            q.AddWhereClauses(setC);
            IDbCommand Command = CommandFromQuery(q);
            Command.Transaction = trans;
            Command.Connection = pConnection;
            Command.ExecuteNonQuery();

            //obj.pID = GetLastInsertID(trans);

            //q.InsertColumns.
            //ExecuteNonQuery(trans, q);
            UpdateTableSync(attr.TableName, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();

        }
        public void DeleteDB(GMTable gmObject)
        {
            if (gmObject.Id == 0)
            {

                throw (new GMException("You cannot delete this item - it's not on the database"));
            }
            Type objType = gmObject.GetType();
            //ReflectionCache[objType].
            GMTableAttribute attr = pReflectionCache[objType];


            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Delete);
            q.pTables.Add(attr.TableName);
            GMQueryClause setC = new GMQueryClause();
            //setC.ClauseType = GMQClauseType.ColumnParameter;
            setC.ClauseOperator = GMQueryClauseOperator.Equals;
            setC.ItemA = GMQueryClauseItemReference.Column(attr.TableName, "id");
            setC.ItemB = GMQueryClauseItemReference.Parameter("id",gmObject.Id);
            
            q.AddWhereClauses(setC);

            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            gmObject.pId = 0;
            UpdateTableSync(attr.TableName, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();

        }
        public GMQueryClause FindInSetClause(GMQueryClauseItemReference searchReference, GMQueryClauseItemReference setReference)
        {

            GMQueryClause w = new GMQueryClause();
            w.ClauseOperator = GMQueryClauseOperator.Like;
            if (searchReference.ReferenceType == GMQueryClauseItemReferenceType.Column)
            {
                w.ItemA = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
                w.ItemA.FunctionParameters.Add(GMQueryClauseItemReference.Literal("%,"));
                w.ItemA.FunctionParameters.Add(GMQueryClauseItemReference.Column(searchReference.TableName, searchReference.ColumnName));
                w.ItemA.FunctionParameters.Add(GMQueryClauseItemReference.Literal(",%"));
            }
            if (searchReference.ReferenceType == GMQueryClauseItemReferenceType.Literal)
            {
                w.ItemA = GMQueryClauseItemReference.Literal("%," + searchReference.LiteralString + ",%");
            }
            if (searchReference.ReferenceType == GMQueryClauseItemReferenceType.Parameter)
            {
                w.ItemA = GMQueryClauseItemReference.Parameter(searchReference.ParameterName, "%," + searchReference.ParameterValue.ToString() + ",%");
            }
            if (searchReference.ReferenceType == GMQueryClauseItemReferenceType.Function)
            {

                w.ItemA = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
                w.ItemA.FunctionParameters.Add(GMQueryClauseItemReference.Literal("%,"));
                w.ItemA.FunctionParameters.Add(searchReference);
                w.ItemA.FunctionParameters.Add(GMQueryClauseItemReference.Literal(",%"));
            }

            if (setReference.ReferenceType == GMQueryClauseItemReferenceType.Column)
            {
                w.ItemB = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
                w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(","));
                w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Column(setReference.TableName, setReference.ColumnName));
                w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(","));
            }
            if (setReference.ReferenceType == GMQueryClauseItemReferenceType.Literal)
            {
                w.ItemB = GMQueryClauseItemReference.Literal("," + setReference.LiteralString + ",");
            }
            if (setReference.ReferenceType == GMQueryClauseItemReferenceType.Parameter)
            {
                w.ItemB = GMQueryClauseItemReference.Parameter(setReference.ParameterName, "," + setReference.ParameterValue.ToString() + ",");
            }
            if (setReference.ReferenceType == GMQueryClauseItemReferenceType.Function)
            {

                w.ItemB = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
                w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(","));
                w.ItemB.FunctionParameters.Add(setReference);
                w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(","));
            }
            return w;
        }
        #endregion

        #region Internal Medium-Level Database Methods
        internal virtual Int64 GetTableSync(Type type)
        {
            return GetTableSync(pReflectionCache[type].TableName);
        }
        internal virtual Int64 GetTableSync(string tableName)
        {
            GMQuery q = new GMQuery(GMQueryCommand.Select);
            q.pTables.Add("tablesync");
            q.AddReturnColumns(GMQueryClauseItemReference.Column("tablesync","modifiedticks"));
            GMQueryClause w = new GMQueryClause();
            w.ClauseOperator = GMQueryClauseOperator.Equals;
            //w.ClauseType = GMQClauseType.ColumnParameter;
            w.ItemA = GMQueryClauseItemReference.Column("tablesync","tablename");
            w.ItemB = GMQueryClauseItemReference.Parameter("tablename",tableName);

            q.AddWhereClauses(w);
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Int64 ticks = Int64.Parse(Command.ExecuteScalar().ToString(),CultureInfo.InvariantCulture);
            Command.Dispose();
            return (ticks);
        }
        internal virtual Int64 GetTableSync(string[] tableNames)
        {
            GMQuery q = new GMQuery(GMQueryCommand.Select);
            q.pTables.Add("tablesync");
            GMQueryClauseItemReference sumFunc = GMQueryClauseItemReference.Function("SUM({0})");
            sumFunc.FunctionParameters.Add(GMQueryClauseItemReference.Column("tablesync", "modifiedticks"));

            q.AddReturnColumns(sumFunc);
            /*
            GMQueryClause w = new GMQueryClause();
            w.ClauseOperator = GMQueryClauseOperator.Like;
            //w.ClauseType = GMQClauseType.ParameterFunction;
            w.ItemA = GMQueryClauseItemReference.Parameter("tablenames", ","+String.Join(",",tableNames)+",");

            w.ItemB = GMQueryClauseItemReference.Function(GMQuerySqlFunction.StringConcatenate);
            w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal("%,"));
            w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Column("tablesync", "tablename"));
            w.ItemB.FunctionParameters.Add(GMQueryClauseItemReference.Literal(",%"));
            //w.ItemA = GMQClauseItemReference.Column("tablesync", "tablename");
            //
             */
            q.AddWhereClauses(
                FindInSetClause(
                    GMQueryClauseItemReference.Parameter("tablenames",String.Join(",", tableNames))
                    ,
                    GMQueryClauseItemReference.Column("tablesync", "tablename")
                    )
                );
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Int64 ticks = Int64.Parse(Command.ExecuteScalar().ToString(), CultureInfo.InvariantCulture);
            Command.Dispose();
            return (ticks);
        }
        internal virtual GMTable[] Select(IDbCommand command,Type type)
        {
             DataTable dT = GetTypedDataTable(command);
            GMTable[] tObjs = new GMTable[dT.Rows.Count];
            GMTableAttribute tAttr = pReflectionCache[type];
            ConstructorInfo cI = tAttr.ConstructorInfo;//

            object o;
            for (int i = 0; i < tObjs.Length; i++)
            {
                tObjs[i] = (GMTable)cI.Invoke(new object[] { this });
                foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tAttr.Columns)
                {
                    if (kp.Value.IsDatabaseColumn)
                    {
                        o = DBToClr(dT.Rows[i][kp.Value.ColumnName], kp.Value);
                        if (kp.Value.ClrType.IsGenericType)
                        {
                            IGMRelation rel = (IGMRelation)kp.Key.GetValue(tObjs[i]);

                            rel.Id =(GMID)o;
                        }
                        else
                        {

                            kp.Key.SetValue(tObjs[i], o);
                        }
                    }
                }

            }
            dT.Dispose();
            return (tObjs);
        }
        internal virtual T[] Select<T>(IDbCommand command) where T : GMTable {

            DataTable dT = GetTypedDataTable(command);
            T[] tObjs = new T[dT.Rows.Count];
            GMTableAttribute tAttr = pReflectionCache[typeof(T)];
            ConstructorInfo cI = tAttr.ConstructorInfo;//

            object o;
            for (int i = 0; i < tObjs.Length; i++)
            {
                tObjs[i] = (T)cI.Invoke(new object[] { this });
                foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tAttr.Columns)
                {
                    if (kp.Value.IsDatabaseColumn)
                    {
                        o = DBToClr(dT.Rows[i][kp.Value.ColumnName], kp.Value);
                        if (kp.Value.ClrType.IsGenericType)
                        {
                            IGMRelation rel = (IGMRelation)kp.Key.GetValue(tObjs[i]);

                            rel.Id =(GMID)o;
                        }
                        else
                        {

                            kp.Key.SetValue(tObjs[i], o);
                        }
                    }
                }

            }
            dT.Dispose();
            return (tObjs);

        }
        #endregion

        #region Internal LowLevel Database Methods
        protected virtual DataTable GetTypedDataTable(IDbCommand command)
        {
            IDataReader reader = command.ExecuteReader();
            DataTable dT = new DataTable();
            dT.Locale = CultureInfo.InvariantCulture;
            for (int i = 0; i < reader.FieldCount; i++)
            {
                dT.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }
            DataRow dR;
            while (reader.Read())
            {
                dR = dT.NewRow();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    dR[i] = reader.GetValue(i);
                }
                dT.Rows.Add(dR);
            }
            reader.Close();
            command.Dispose();
            return dT;
        }
        protected virtual void ExecuteNonQuery(IDbTransaction transaction, GMQuery query)
        {
            IDbCommand Command = CommandFromQuery(query);
            Command.Transaction = transaction;
            Command.ExecuteNonQuery();
            Command.Dispose();
        }
        protected virtual void ExecuteNonQuery(GMQuery query)
        {
            IDbTransaction trans = pConnection.BeginTransaction();

            ExecuteNonQuery(trans, query);
            trans.Commit();
        }

        protected virtual GMID GetLastInsertId(IDbCommand command, string tableName)
        {
            throw (new GMException(ErrorStrings.GMMissingImplementationException));
        }
        protected void UpdateTableSync(string tableName, IDbCommand command)
        {
            
            GMQuery q = new GMQuery(GMQueryCommand.Update);
           
            q.pTables.Add("tablesync");

            GMQueryClause whereclause = new GMQueryClause();
            whereclause.ClauseOperator = GMQueryClauseOperator.Equals;
            //whereclause.ClauseType = GMQClauseType.ColumnParameter;
            whereclause.ItemA = GMQueryClauseItemReference.Column("tablesync","tablename");
            whereclause.ItemB = GMQueryClauseItemReference.Parameter("tablename", tableName);

            q.AddWhereClauses(whereclause);

            GMQueryClause setclause = new GMQueryClause();
            setclause.ClauseOperator = GMQueryClauseOperator.Equals;
            //setclause.ClauseType = GMQClauseType.ColumnParameter;
            setclause.ItemA = GMQueryClauseItemReference.Column("tablesync", "modifiedticks");
            setclause.ItemB = GMQueryClauseItemReference.Parameter("modifiedticks", DateTime.UtcNow.Ticks);

            q.AddSetClauses(setclause);

            SetCommandFromQuery(q, command);
            //Command.Parameters["newticks"] = DateTime.UtcNow.Ticks;
            //Command.Parameters["tablename"] = tableName;
            command.ExecuteNonQuery();
        }

#endregion

        
        

        #region Link Methods
        public void DeleteLink(GMTable parent, GMTable reference, GMTable index)
        {
            if (reference.Id == 0)
            {
                throw (new GMException("You cannot unlink an item that is not on the database"));
            }
            if (index != null)
            {
                if (index.Id == 0)
                {
                    throw (new GMException("You cannot unlink an item that is not on the database (index)"));
                }
            }
            GMLinkTable lT = LinkTableFor(parent.GetType(), reference.GetType(), (index == null) ? null : index.GetType());
            if (lT == null)
            {
                throw (new GMException("This relation doesn't use link tables... You cannot unlink it up this way as you'd be unrefencing another unlink."));
            }
            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Delete);

            q.pTables.Add(lT.Name);
            string cName;
            cName = pReflectionCache[parent.GetType()].TableName + "_id";
            GMQueryClause c;
            c = new GMQueryClause();
            c.ClauseOperator = GMQueryClauseOperator.Equals;
            //c.ClauseType = GMQClauseType.ColumnParameter;
            c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            c.ItemB = GMQueryClauseItemReference.Parameter(cName, parent.Id);
            q.AddWhereClauses(c);

            cName = pReflectionCache[reference.GetType()].TableName + "_id";
            c = new GMQueryClause();
            c.ClauseOperator = GMQueryClauseOperator.Equals;
            //c.ClauseType = GMQClauseType.ColumnParameter;
            c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            c.ItemB = GMQueryClauseItemReference.Parameter(cName, reference.Id);
            q.AddWhereClauses(c);
            if (index != null && !lT.UseIndexField)
            {

                cName = pReflectionCache[index.GetType()].TableName + "_id";
                c = new GMQueryClause();
                c.ClauseOperator = GMQueryClauseOperator.Equals;
                //c.ClauseType = GMQClauseType.ColumnParameter;
                c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
                c.ItemB = GMQueryClauseItemReference.Parameter(cName, index.Id);
                q.AddWhereClauses(c);
            }
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            UpdateTableSync(lT.Name, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();
        }
        public void DeleteLink(GMTable parent, GMTable reference, object parameter)
        {
            if (reference.Id == 0)
            {
                throw (new GMException("You cannot unlink an item that is not on the database"));
            }
            GMLinkTable lT = LinkTableFor(parent.GetType(), reference.GetType(), parameter.GetType());
            if (lT == null)
            {
                throw (new GMException("This relation doesn't use link tables... You cannot unlink it up this way as you'd be unrefencing another unlink."));
            }
            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Delete);

            q.pTables.Add(lT.Name);
            string cName;
            cName = pReflectionCache[parent.GetType()].TableName + "_id";
            GMQueryClause c;
            c = new GMQueryClause();
            c.ClauseOperator = GMQueryClauseOperator.Equals;
            //c.ClauseType = GMQClauseType.ColumnParameter;
            c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            c.ItemB = GMQueryClauseItemReference.Parameter(cName, parent.Id);
            q.AddWhereClauses(c);

            cName = pReflectionCache[reference.GetType()].TableName + "_id";
            c = new GMQueryClause();
            c.ClauseOperator = GMQueryClauseOperator.Equals;
            //c.ClauseType = GMQClauseType.ColumnParameter;
            c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            c.ItemB = GMQueryClauseItemReference.Parameter(cName, reference.Id);
            q.AddWhereClauses(c);
            if (parameter != null && !lT.UseIndexField)
            {

                cName = lT.Parameter.ColumnName;
                c = new GMQueryClause();
                c.ClauseOperator = GMQueryClauseOperator.Equals;
                //c.ClauseType = GMQClauseType.ColumnParameter;
                c.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
                c.ItemB = GMQueryClauseItemReference.Parameter(cName, parameter);
                q.AddWhereClauses(c);
            }
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            UpdateTableSync(lT.Name, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();
        }
        public void DeleteLink(GMTable parent, GMTable reference)
        {
            DeleteLink(parent, reference, null);
        }
        public void CreateLink(GMTable parent, GMTable reference)
        {
            CreateLink(parent, reference, null);
        }
        public void CreateLink(GMTable parent, GMTable reference, object parameter)
        {
            if (reference.Id == 0)
            {
                throw (new GMException("You cannot link an item that is not on the database"));
            }
            GMLinkTable lT = LinkTableFor(parent.GetType(), reference.GetType(), parameter.GetType());
            if (lT == null)
            {
                throw (new GMException("This relation doesn't use link tables... You cannot link it up this way as you'd be unrefencing another link."));
            }
            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Insert);
            q.pTables.Add(lT.Name);
            string cName;
            cName = pReflectionCache[parent.GetType()].TableName + "_id";
            GMQueryClause clause;
            clause = new GMQueryClause();
            clause.ClauseOperator = GMQueryClauseOperator.Equals;
            //clause.ClauseType = GMQClauseType.ColumnParameter;
            clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            clause.ItemB = GMQueryClauseItemReference.Parameter(cName, parent.Id);
            q.AddSetClauses(clause);

            cName = pReflectionCache[reference.GetType()].TableName + "_id";
            clause = new GMQueryClause();
            clause.ClauseOperator = GMQueryClauseOperator.Equals;
            //clause.ClauseType = GMQClauseType.ColumnParameter;
            clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            clause.ItemB = GMQueryClauseItemReference.Parameter(cName, reference.Id);
            q.AddSetClauses(clause);
            if (parameter != null)
            {

                cName = lT.Parameter.ColumnName;
                clause = new GMQueryClause();

                clause.ClauseOperator = GMQueryClauseOperator.Equals;
                //clause.ClauseType = GMQClauseType.ColumnParameter;
                clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
                clause.ItemB = GMQueryClauseItemReference.Parameter(cName, parameter);
                q.AddSetClauses(clause);
            
            }
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            UpdateTableSync(lT.Name, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();

        }
                
        public void CreateLink(GMTable parent, GMTable reference, GMTable index)
        {
            if (reference.Id == 0)
            {
                throw (new GMException("You cannot link an item that is not on the database"));
            }
            if (index != null)
            {
                if (index.Id == 0)
                {
                    throw (new GMException("You cannot link an item that is not on the database (index)"));
                }
            }
            GMLinkTable lT = LinkTableFor(parent.GetType(), reference.GetType(), (index == null) ? null : index.GetType());

            if (lT == null)
                lT = LinkTableFor(reference.GetType(), parent.GetType(), (index == null) ? null : index.GetType());
            
            
            if (lT == null)
            {
                throw (new GMException("This relation doesn't use link tables... You cannot link it up this way as you'd be unrefencing another link."));
            }
            IDbTransaction trans = pConnection.BeginTransaction();
            GMQuery q = new GMQuery(GMQueryCommand.Insert);
            q.pTables.Add(lT.Name);
            string cName;
            cName = pReflectionCache[parent.GetType()].TableName + "_id";
            GMQueryClause clause;
            clause = new GMQueryClause();
            clause.ClauseOperator = GMQueryClauseOperator.Equals;
            //clause.ClauseType = GMQClauseType.ColumnParameter;
            clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            clause.ItemB = GMQueryClauseItemReference.Parameter(cName, parent.Id);
            q.AddSetClauses(clause);

            cName = pReflectionCache[reference.GetType()].TableName + "_id";
            clause = new GMQueryClause();
            clause.ClauseOperator = GMQueryClauseOperator.Equals;
            //clause.ClauseType = GMQClauseType.ColumnParameter;
            clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
            clause.ItemB = GMQueryClauseItemReference.Parameter(cName, reference.Id);
            q.AddSetClauses(clause);
            if (index != null&&!lT.UseIndexField)
            {
                cName = pReflectionCache[index.GetType()].TableName + "_id";
                clause = new GMQueryClause();

                clause.ClauseOperator = GMQueryClauseOperator.Equals;
                ///clause.ClauseType = GMQClauseType.ColumnParameter;
                clause.ItemA = GMQueryClauseItemReference.Column(lT.Name, cName);
                clause.ItemB = GMQueryClauseItemReference.Parameter(cName, index.Id);
                q.AddSetClauses(clause);
            
            }
            IDbCommand Command = CommandFromQuery(q);
            Command.Connection = pConnection;
            Command.Transaction = trans;
            Command.ExecuteNonQuery();
            UpdateTableSync(lT.Name, Command);
            trans.Commit();

            Command.Dispose();
            trans.Dispose();
        }
        #endregion 

        #region Internal Link Methods
        public GMLinkTable LinkTableFor(Type parentType, Type relationType, Type indexType)
        {
            GMLinkTable LinkTableRelation = null;
            foreach (GMLinkTable lT in pLinkTables)
            {
                if (lT.Master.TableType == parentType
                    &&
                    lT.Relation.TableType == relationType)
                {
                    if (lT.Index == null && indexType == null)
                    {

                        LinkTableRelation = lT;
                    }
                    else if (lT.Index!=null) {
                    
                        if (lT.Index.TableType == indexType)
                        {

                            LinkTableRelation = lT;
                        }
                    } else {
                        
                            LinkTableRelation = lT;
                    }
                }
            }
            return (LinkTableRelation);
        }
        public GMQuery RelationLookupQuery(Type parentType, Type refType, Type indexType)
        {

            GMLinkTable LinkTableRelation = LinkTableFor(parentType, refType, indexType);
            if (LinkTableRelation == null)
                LinkTableRelation = LinkTableFor(refType, parentType, indexType);
            if (LinkTableRelation != null)
            {
                GMQuery q = new GMQuery(GMQueryCommand.Select);
                q.pTables.Add(pReflectionCache[refType].TableName);
                q.pTables.Add(LinkTableRelation.Name);
                GMQueryClause c = new GMQueryClause();
                c.ClauseOperator = GMQueryClauseOperator.Equals;
                //c.ClauseType = GMQClauseType.ColumnParameter;
                c.ItemA = GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[parentType].TableName + "_id");
                c.ItemB = GMQueryClauseItemReference.Parameter("parentID",null);
                
                q.AddWhereClauses(c);
                if (indexType != null && !LinkTableRelation.UseIndexField)
                {
                    c = new GMQueryClause();
                    c.ClauseOperator = GMQueryClauseOperator.Equals;
                    //c.ClauseType = GMQClauseType.ColumnParameter;
                    //c.TableA = LinkTableRelation.Name;
                    
                    if (LinkTableRelation.Parameter != null)
                    {

                        //c.KeyA = ;
                        c.ItemA = GMQueryClauseItemReference.Column(LinkTableRelation.Name, LinkTableRelation.Parameter.ColumnName);
                
                    }
                    else {

                        //c.KeyA = pReflectionCache[indexType].TableName + "_id";
                        c.ItemA = GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[parentType].TableName + "_id");
                
                    }

                    c.ItemB = GMQueryClauseItemReference.Parameter("indexID", null);

                    q.AddWhereClauses(c);

                }
                else if (indexType != null && LinkTableRelation.UseIndexField)
                {
                    c = new GMQueryClause();
                    c.ClauseOperator = GMQueryClauseOperator.Equals;
                    //c.ClauseType = GMQClauseType.ColumnParameter;
                    c.ItemA = GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, pReflectionCache[indexType].TableName + "_id");

                    c.ItemB = GMQueryClauseItemReference.Parameter("indexID", null);
                    q.AddWhereClauses(c);
                    c = new GMQueryClause();
                    c.ClauseOperator = GMQueryClauseOperator.Equals;
                    //c.ClauseType = GMQClauseType.ColumnColumn;
                    c.ItemA = GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, "id");

                    c.ItemB = GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[refType].TableName+"_id");
                    q.AddWhereClauses(c);
                }

       
                q.AddReturnColumns(GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[refType].TableName + "_id","id"));
                
                return (q);
            }
            else
            {
                //If it's a reverse relation
                GMQuery q = new GMQuery(GMQueryCommand.Select);
                q.pTables.Add(pReflectionCache[refType].TableName);
                q.pTables.Add(pReflectionCache[parentType].TableName);

                GMQueryClause c = new GMQueryClause();
                c.ClauseOperator = GMQueryClauseOperator.Equals;
                //c.ClauseType = GMQClauseType.ColumnParameter;
                c.ItemA = GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, pReflectionCache[parentType].TableName + "_id");
                c.ItemB = GMQueryClauseItemReference.Parameter("parentID", null);

                q.AddWhereClauses(c);
                q.AddReturnColumns(GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, "id", "id"));
                
                return (q);
            }
        }

        public GMQuery RelationLookupQueryAllKeys(Type parentType, Type refType, Type indexType)
        {

            GMLinkTable LinkTableRelation = LinkTableFor(parentType, refType, indexType);
            if (LinkTableRelation == null)
                LinkTableRelation = LinkTableFor(refType, parentType, indexType);
            if (LinkTableRelation                 ==null)
            {
                throw (new GMException("MUST be a Link Table relation to have keys"));
            }
            GMQuery q = new GMQuery(GMQueryCommand.Select);
            q.pTables.Add(pReflectionCache[refType].TableName);
            q.pTables.Add(LinkTableRelation.Name);
            GMQueryClause c = new GMQueryClause();
            c.ClauseOperator = GMQueryClauseOperator.Equals;
            //c.ClauseType = GMQClauseType.ColumnParameter;
            c.ItemA = GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[parentType].TableName + "_id");
            c.ItemB = GMQueryClauseItemReference.Parameter("parentID", null);

            q.AddWhereClauses(c);
            
            if (indexType != null && !LinkTableRelation.UseIndexField)
            {
             
                if (LinkTableRelation.Parameter != null)
                {

                    q.AddReturnColumns(GMQueryClauseItemReference.Column(LinkTableRelation.Name, LinkTableRelation.Parameter.ColumnName, "id"));

                }
                else
                {

                    q.AddReturnColumns(GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[indexType].TableName + "_id", "id"));

                }

            }
            else if (indexType != null && LinkTableRelation.UseIndexField)
            {

                c = new GMQueryClause();
                c.ClauseOperator = GMQueryClauseOperator.Equals;
                //c.ClauseType = GMQClauseType.ColumnColumn;
                c.ItemA = GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, "id");

                c.ItemB = GMQueryClauseItemReference.Column(LinkTableRelation.Name, pReflectionCache[refType].TableName + "_id");
                q.AddWhereClauses(c);
                q.AddReturnColumns(GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, pReflectionCache[indexType].TableName + "_id", "id"));
            }
            if (LinkTableRelation.Parameter != null)
            {
                q.AddGroupClause(GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, LinkTableRelation.Parameter.ColumnName));
            
                }
            else
            {
                q.AddGroupClause(GMQueryClauseItemReference.Column(pReflectionCache[refType].TableName, pReflectionCache[indexType].TableName + "_id"));
            
            }
                return (q);
       
    
        }
#endregion


        #region Internal Startup Methods

        public void GenerateReflectionCache()
        {
            Assembly[] assms = AppDomain.CurrentDomain.GetAssemblies();
            pReflectionCache = new Dictionary<Type, GMTableAttribute>();
            pIDCache = new Dictionary<Type, Dictionary<GMID, GMTable>>();
            pNameCache = new Dictionary<Type, Dictionary<string, GMTableNamed>>();
            pIDCacheTicks = new Dictionary<Type, Dictionary<GMID, Int64>>();
            pNameCacheTicks = new Dictionary<Type, Dictionary<string, Int64>>();


            foreach (Assembly assm in assms)
            {
                if (!assm.GlobalAssemblyCache)
                {
                    foreach (Type ty in assm.GetTypes())
                    {
                        GMTableAttribute dbTabAttr = (GMTableAttribute)Attribute.GetCustomAttribute(ty, typeof(GMTableAttribute));
                        if (dbTabAttr != null)
                        {
                            //dbTabAttr.Columns = GetColumns(ty);
                            dbTabAttr._TableType = ty;
                            pIDCache.Add(ty, new Dictionary<GMID, GMTable>());
                            pIDCacheTicks.Add(ty, new Dictionary<GMID, Int64>());
                            if (ty.IsSubclassOf(typeof(GMTableNamed)))
                            {
                                pNameCache.Add(ty, new Dictionary<string, GMTableNamed>());
                                pNameCacheTicks.Add(ty, new Dictionary<string, Int64>());
                            }
                            dbTabAttr.ConstructorInfo = ty.GetConstructor(pStandardConstructorTypes);
                            pReflectionCache.Add(ty, dbTabAttr);
                        }
                    }
                }
            }
            foreach (KeyValuePair<Type, GMTableAttribute> kp in pReflectionCache)
            {
                kp.Value._Columns = GetColumns(kp.Key,kp.Value);
            }
            List<KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>> SimpleRelationColumns = new List<KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>>();
            List<KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>> ReverseRelationColumns = new List<KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>>();
            List<GMColumnBaseAttribute> LinkRelationColumns = new List<GMColumnBaseAttribute>();
            pLinkTables = new Collection<GMLinkTable>();
            Console.WriteLine("Creating relationships...");
            bool IsSimple = false;
            //bool FoundRelationColumn = false;
            //bool FoundIndexColumn = false;
            foreach (KeyValuePair<Type, GMTableAttribute> tab in pReflectionCache)
            {
                foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> col in tab.Value._Columns)
                {
                    if (!col.Value.pIsDatabaseColumn)
                    {
                        //RelationColumns.Add(tab.Key, col.Value);
                        //So this column is not on the database... It is a generic collection referencing...
                        //Console.WriteLine(tab.Value.TableName+" - "+col.Key.Name);
                        Type[] ts = col.Key.FieldType.GetGenericArguments();
                        IsSimple = false;
                        if (ts.Length == 1)
                        {

                            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> o_col in pReflectionCache[ts[0]]._Columns) {

                                Type[] ots = o_col.Key.FieldType.GetGenericArguments();
                                //if (o_col.Value.pIsDatabasedColumn && o_col.Value.pGMRelationType == ts[0])


                                //if (o_col.Value.pIsDatabasedColumn && 
                                //    tab.Key == ots[0])

                                    if (o_col.Value.pIsDatabaseColumn && o_col.Value.pGMRelationType == ts[0]
                                        && tab.Key == ots[0])


                                //if (o_col.Value.pIsDatabasedColumn && o_col.Value.pGMRelationType ==tab.Key)
                                {
                                    Console.WriteLine("Reverse relationship " + tab.Key.Name + "." + col.Key.Name + " (" + o_col.Key.Name + ") in " + ts[0].Name);
                                    //col.Value.pGMRelationType = ts[0];
                                    ReverseRelationColumns.Add(new KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>(col.Value, o_col.Value));
                                    IsSimple = true;
                                }
                                
                            }
                            if (IsSimple)
                            {
                                ;
                            } else {
                                LinkRelationColumns.Add(col.Value);
            
                            }
                        }
                        else if (ts.Length==2)
                        {
                            LinkRelationColumns.Add(col.Value);
            
                        }

                    }
                    else if (col.Value.pGMParentType!=null)
                    {
                        SimpleRelationColumns.Add(new KeyValuePair<GMColumnBaseAttribute, GMColumnBaseAttribute>(col.Value, col.Value));
                    }
                }
            }
            /*
            Console.WriteLine("Simple relations");
            foreach (KeyValuePair<GMColumnAttributeBase, GMColumnAttributeBase> relation in SimpleRelationColumns)
            {
                Console.WriteLine("\t" + relation.Key._GMTableAttribute.TableName);
                Console.WriteLine("\t\t" + relation.Key._CLRField.Name);
                Console.WriteLine("\t\tUses a query on");
                Console.WriteLine("\t\t\t" + ReflectionCache[relation.Value._GMRelationType].TableName);
                Console.WriteLine("\t\t\t" + relation.Value._ColumnName + " = " + ReflectionCache[relation.Value._GMRelationType].TableName + ".id");
                
                
                GMQuery gmq = new GMQuery();
                gmq.Command = GMQueryCommand.Select;
                gmq.FromTables.Add(ReflectionCache[relation.Value._GMRelationType].TableName);
                gmq.TypeToGetAttr = ReflectionCache[relation.Value._GMRelationType];
                GMQuery.GMComparisonOrAssignmentClause gmwc = new GMQuery.GMComparisonOrAssignmentClause();
                gmwc.IsParameterClause = true;
                gmwc.TableA = ReflectionCache[relation.Value._GMRelationType].TableName;
                gmwc.KeyA = "id";
                gmwc.ParameterName = ReflectionCache[relation.Value._GMRelationType].TableName + "_id";
                gmq.Where.Add(gmwc);
                Console.WriteLine("QUERY");
                Console.WriteLine(gmq.ToString());
                Console.WriteLine();
            }
            Console.WriteLine("Reverse relations");
            foreach (KeyValuePair<GMColumnAttributeBase, GMColumnAttributeBase> relation in ReverseRelationColumns)
            {
                Console.WriteLine("\t" + relation.Key._GMTableAttribute.TableName);
                Console.WriteLine("\t\t" + relation.Key._CLRField.Name);
                Console.WriteLine("\t\tUses a query on");
                Console.WriteLine("\t\t\t" + relation.Value._GMTableAttribute.TableName);
                Console.WriteLine("\t\t\t" + relation.Value._ColumnName+" = "+relation.Key._GMTableAttribute.TableName+".id");

                GMQuery gmq = new GMQuery();
                gmq.Command = GMQueryCommand.Select;
                gmq.FromTables.Add(relation.Value._GMTableAttribute.TableName);
                gmq.TypeToGetAttr = relation.Value._GMTableAttribute;
                GMQuery.GMComparisonOrAssignmentClause gmwc = new GMQuery.GMComparisonOrAssignmentClause();
                gmwc.IsParameterClause = true;
                gmwc.TableA = relation.Value._GMTableAttribute.TableName;
                gmwc.KeyA = relation.Value._ColumnName;
                gmwc.ParameterName = relation.Value._ColumnName;
                gmq.Where.Add(gmwc);
                Console.WriteLine("QUERY");
                Console.WriteLine(gmq.ToString());
                Console.WriteLine();

            }
            */
            List<string> createdLinkTables = new List<string>();
            //Console.WriteLine("Link Table relations");
            foreach (GMColumnBaseAttribute col in LinkRelationColumns)
            {
                //Console.WriteLine(col._GMTableAttribute._TableType.ToString());
                //Console.WriteLine(col._CLRField.Name);
                //Console.WriteLine(col._CLRType.Name);
                List<GMTableAttribute> LinkTableNameL = new List<GMTableAttribute>();
                string LinkTableName = "";
                LinkTableNameL.Add(col.pGMTableAttribute);
                //Console.WriteLine(col.CLRType.GetGenericParameterConstraints()[0].ToString());
                Type[] types = col.pClrField.FieldType.GetGenericArguments();
                GMTableAttribute RelTypeAttr;
                GMTableAttribute IndexTypeAttr = null;
                bool Duplicates = false;
                bool Unique = false;
                bool UsesIndexField = false;
                //string ParameterName = null;
                
                //GMRelationColumnAttribute rca = (GMRelationColumnAttribute)GMRelationColumnAttribute.GetCustomAttribute(col._CLRField, typeof(GMRelationColumnAttribute));
                GMRelationColumnAttribute rca = col as GMRelationColumnAttribute;
                if (rca != null)
                {
                    Duplicates = rca.AllowDuplicates;
                    UsesIndexField = rca.IndexIsInRelation;
                }
                if (col.pClrType == typeof(GMIndexedRelationDictionary<,>))
                {
                    Unique = true;
                    RelTypeAttr = pReflectionCache[types[1]];
                    LinkTableNameL.Add(RelTypeAttr);
                } else if (col.pClrType == typeof(GMIndexedRelationsDictionary<,>))
                {
                    RelTypeAttr = pReflectionCache[types[1]];
                    LinkTableNameL.Add(RelTypeAttr);
                }
                else if (col.pClrType == typeof(GMParameterRelationDictionary<,>))
                {

                    RelTypeAttr = pReflectionCache[types[1]];
                    LinkTableNameL.Add(RelTypeAttr);
                }
                else
                {
                    RelTypeAttr = pReflectionCache[types[0]];
                    LinkTableNameL.Add(RelTypeAttr);
                }
                LinkTableNameL.Sort(
                    new Comparison<GMTableAttribute>(
                    delegate(GMTableAttribute a,GMTableAttribute b) {

                        return (System.Collections.Comparer.Default.Compare(a.Priority, b.Priority));
                    }
                        )
                        );
                LinkTableName = LinkTableNameL[0].TableId + "_" + LinkTableNameL[1].TableId;
                GMColumnBaseAttribute ParaColumnAttr = null;
                if (col.pClrType == typeof(GMParameterRelationDictionary<,>))
                {
                    UsesIndexField = false;
                }
                if (types.Length == 2 && !UsesIndexField)
                {
                    if (col.pClrType == typeof(GMParameterRelationDictionary<,>))
                    {
                        ParaColumnAttr = col as GMColumnBaseAttribute;
                        //ParameterName = rca.ParameterName;
                        LinkTableName += "_" + rca.ParameterName;

                        rca.pClrType = types[0];
                        rca.pColumnName = rca.ParameterName;
                        SetDatabaseSchemaField(types[0], ref ParaColumnAttr);
                        //rca.SetDatabase(ParaColumnAttr._DatabaseTypeID, ParaColumnAttr._DatabaseDefaultValue);
                       
                    } else {
                        IndexTypeAttr = pReflectionCache[types[0]];
                        LinkTableName += "_" + IndexTypeAttr.TableId;
                    }
                }
                /*
                Console.WriteLine(LinkTableName);
                GMQuery gmq = new GMQuery();
                gmq.Command = GMQueryCommand.Select;
                gmq.FromTables.Add(RelTypeAttr.TableName);
                gmq.FromTables.Add(LinkTableName);
                gmq.TypeToGetAttr = RelTypeAttr;
                GMQuery.GMComparisonOrAssignmentClause gmwc = new GMQuery.GMComparisonOrAssignmentClause();
                gmwc.IsParameterClause = true;
                gmwc.TableA = LinkTableName;
                gmwc.KeyA = col._GMTableAttribute.TableName+"_id";
                gmwc.ParameterName = col._GMTableAttribute.TableName + "_id";
                gmq.Where.Add(gmwc);
                gmwc = new GMQuery.GMComparisonOrAssignmentClause();
                gmwc.IsParameterClause = false;
                gmwc.TableA = LinkTableName;
                gmwc.KeyA = RelTypeAttr.TableName + "_id";
                gmwc.TableB = RelTypeAttr.TableName;
                gmwc.KeyB = "id";
                gmq.Where.Add(gmwc);
                if (types.Length == 2)
                {

                    gmwc = new GMQuery.GMComparisonOrAssignmentClause();
                    gmwc.IsParameterClause = true;
                    gmwc.TableA = LinkTableName;
                    gmwc.KeyA = IndexTypeAttr.TableName + "_id";
                    gmwc.ParameterName = IndexTypeAttr.TableName + "_id";
                    gmq.Where.Add(gmwc);
                }
                Console.WriteLine("QUERY");
                Console.WriteLine(gmq.ToString());
                Console.WriteLine();
                */

                if (!createdLinkTables.Contains(LinkTableName))
                {
                    pLinkTables.Add(new GMLinkTable(LinkTableName, col.pGMTableAttribute, RelTypeAttr, IndexTypeAttr, ParaColumnAttr, Duplicates, Unique, UsesIndexField));
                    createdLinkTables.Add(LinkTableName);
                }

            }
        }


        void CreateStaticValues()
        {
            /*
            //Create the static values
            Type attrType = typeof(GMStaticValueAttribute);
            GMStaticValueAttribute attr;
            Type[] constructorTypes = new Type[] { typeof(GMCommunicator) };
            ConstructorInfo cI;
            GMTableNamed gmt;
            foreach (KeyValuePair<Type, GMTableAttribute> tab in pReflectionCache)
            {
                
                attr = (GMStaticValueAttribute)Attribute.GetCustomAttribute(tab.Key, attrType);
                if (attr != null)
                {
                    foreach (string str in attr.Values)
                    {
                        cI = tab.Key.GetConstructor(constructorTypes);
                        gmt = (GMTableNamed)cI.Invoke(new object[] { this });
                        gmt.Name = str;
                        this.CreateDB(gmt);
                    }
                }
                
                
            }
             */
        }
        protected Dictionary<FieldInfo, GMColumnBaseAttribute> GetColumns(Type type,GMTableAttribute tableAttribute)
        {
            //Maybe this should be cached?
            Dictionary<FieldInfo, GMColumnBaseAttribute> fIs = new Dictionary<FieldInfo, GMColumnBaseAttribute>();
            Type TY = type;
            FieldInfo[] _fIs;
            List<FieldInfo> Pairs_a = new List<FieldInfo>();
            List<GMColumnBaseAttribute> Pairs_b = new List<GMColumnBaseAttribute>();
            while (TY != typeof(object))
            {
                _fIs = TY.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);

                for (int i = _fIs.Length - 1; i >= 0; i--)
                {
                    if (_fIs[i].DeclaringType == TY)
                    {
                        GMColumnBaseAttribute dbColAttr = (GMColumnBaseAttribute)Attribute.GetCustomAttribute(_fIs[i], typeof(GMColumnBaseAttribute));
                        if (dbColAttr != null)
                        {

                            Pairs_a.Add(_fIs[i]);
                            if (dbColAttr.pIsDatabaseColumn)
                            {
                                dbColAttr.pClrType = _fIs[i].FieldType;
                                dbColAttr.pColumnName = dbColAttr.pColumnPrefix+_fIs[i].Name.Substring(1).ToLower(CultureInfo.InvariantCulture);
                                //dbColAttr._DatabaseTypeID = GetDatabaseTypeID(_fIs[i].FieldType, dbColAttr);
                                SetDatabaseSchemaField(_fIs[i].FieldType, ref dbColAttr);
                                if (dbColAttr.pClrType.IsGenericType)
                                {
                                    Type ta = dbColAttr.pClrType.GetGenericTypeDefinition();
                                    Type tb = typeof(GMRelation<>);

                                    if (ta == tb)
                                    {
                                        dbColAttr.pGMRelationAttr = pReflectionCache[dbColAttr.pClrType.GetGenericArguments()[0]];
                                        dbColAttr.pColumnName = dbColAttr.pColumnPrefix + dbColAttr.pGMRelationAttr.TableName + "_id";
                                        dbColAttr.pGMRelationType = type;//dbColAttr.pCLRType.GetGenericArguments()[0];
                                        dbColAttr.pGMParentType = type;
                                        dbColAttr.pColumnType = GMColumnType.Relation;
                                        dbColAttr.Key = true;
                                    }
                                }

                            }
                            else
                            {
                                dbColAttr.pClrType = _fIs[i].FieldType.GetGenericTypeDefinition();
                                
                            }
                            dbColAttr.pGMTableAttribute = tableAttribute;
                            dbColAttr.pClrField = _fIs[i];
                            Pairs_b.Add(dbColAttr);

                            //fIs.Add(_fIs[i], dbColAttr);
                        }
                    }
                }
                TY = TY.BaseType;
                //fIs.AddRange(_fIs);

            }
            //Got to work up the inhertence tree, we like orders going up
            Pairs_a.Reverse();
            Pairs_b.Reverse();
            for (int i = 0; i < Pairs_a.Count; i++)
            {
                fIs.Add(Pairs_a[i], Pairs_b[i]);
            }

            return (fIs);
        }

        #endregion
    }
}
